package zhtt;

import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * Created by zhtt on 2017/4/10.
 * 聚合函数
 * 注意区分 count(*)与 count(name)的不同。count(*)与 count(id)等价，均表示未空记录数。
 而 count(name)则表示字段 name 的非空记录数。
 */
public class JunitTest_Count {

    private Session session;

    @Before
    public void setUp(){
        session=HbnUtil.getSession();
    }

    @Test
    public void sql(){
        try{
            session.beginTransaction();
            String sql="select count(*) from zhtt0_student";
//            Object student=  session.createSQLQuery(sql).uniqueResult();//变量名用类名小写报错，奇怪
            Object total1=  session.createSQLQuery(sql).uniqueResult();
            System.out.println(total1);

            String sql2="select count(name) from zhtt0_student";//如果name为null则不纳入统计内
            Object total2=session.createSQLQuery(sql2).uniqueResult();
            System.out.println(total2);
            session.getTransaction().commit();
        }catch (Exception e){
            e.printStackTrace();;
        }
    }

    @Test
    public void hql(){
        try{
            session.beginTransaction();
            String sql="select count(*) from Student";
//            Object student=  session.createSQLQuery(sql).uniqueResult();//变量名用类名小写报错，奇怪
            Object total1=  session.createQuery(sql).uniqueResult();
            System.out.println(total1);

            String sql2="select count(name) from Student";//如果name为null则不纳入统计内
            Object total2=session.createQuery(sql2).uniqueResult();
            System.out.println(total2);
            session.getTransaction().commit();
        }catch (Exception e){e.printStackTrace();}
    }

    @Test
    public void qbc(){
        try{
            session.beginTransaction();
            Object total=session.createCriteria(Student.class)
                    .setProjection(Projections.count("name"))
                    .uniqueResult();
            System.out.println(total);
            session.getTransaction().commit();
        }catch (Exception e){e.printStackTrace();}
    }
}
